package freecomm.study.notes;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class NoteDatabaseHelper extends SQLiteOpenHelper
{
    private static final String CREAT_TABLE_SQL = ""
            + "CREATE TABLE NOTE("
            + "_id integer PRIMARY KEY,"
            + "_content text NOT NULL,"
            + "_color integer NOT NULL DEFAULT(0),"
            + "_time DATETIME DEFAULT(DATETIME(CURRENT_TIMEStAMP,'localtime')),"
            + "_widgetid integer NOT NULL DEFAULT(0)" + ")";
    private SQLiteDatabase mDB;

    public NoteDatabaseHelper(Context context)
    {
        super(context, "note", null, 1);
    }
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        // TODO Auto-generated method stub
        mDB = db;
        db.execSQL(CREAT_TABLE_SQL);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
    }

    public void close()
    {
        SQLiteDatabase db = getWritableDatabase();
        db.close();
    }

    public void insert(String mNoteContent)
    {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("INSERT INTO note (_content)values(\"" + mNoteContent
                + "\")");
    }

    public void insert(String mNoteContent, int mColor)
    {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("INSERT INTO note (_content,_color)values(\"" + mNoteContent
                + "\"," + mColor + ")");
    }

    public void insert(String mNoteContent, int mColor, int widgetID)
    {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("INSERT INTO note (_content,_color,_widgetid)values(\""
                + mNoteContent + "\"," + mColor + "," + widgetID + ")");
    }

    public Cursor query()
    {
        SQLiteDatabase db = getWritableDatabase();
        Cursor mCursor = db.query("note", null, "_widgetid=0", null, null,
                null, "_time");
        return mCursor;
    }

    public Cursor query(int mId)
    {
        SQLiteDatabase db = getWritableDatabase();
        Cursor mCursor = db.query("note", null, "_id=" + mId, null, null, null,
                "_time");
        return mCursor;

    }

    public void delete(int mId)
    {
        SQLiteDatabase db = getWritableDatabase();
        db.delete("note", "_id=?", new String[] { mId + "" });
    }

    public void deleteByWidget(int mWidgetId)
    {
        SQLiteDatabase db = getWritableDatabase();
        db.delete("note", "_widgetid=?", new String[] { mWidgetId + "" });
    }

    public Cursor queryByWidgetId(int mWidgetId)
    {
        SQLiteDatabase db = getWritableDatabase();
        Cursor mCursor = db.query("note", null, "_widgetid=" + mWidgetId, null,
                null, null, "_time");
        return mCursor;
    }

    public void updateByWidgetId(String mNoteContent, int mColor, String mTime,
            int mWidgetId)
    {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues mCV = new ContentValues();
        mCV.put("_content", mNoteContent);
        mCV.put("_color", mColor);
        mCV.put("_time", mTime);
        db.update("note", mCV, "_widgetid=?", new String[] { mWidgetId + "" });
    }

    public void update(int mId, String mNoteContent, int mColor, String mTime)
    {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues mCV = new ContentValues();
        mCV.put("_content", mNoteContent);
        mCV.put("_color", mColor);
        mCV.put("_time", mTime);
        db.update("note", mCV, "_id=?", new String[] { mId + "" });
    }
}
